import json

from sqlalchemy import func

from flaskapp.entity.Book import Book
from flaskapp.entity.Record import Record
from flaskapp.util.Redprint import Redprint
from flaskapp.util.Result import Result

api = Redprint('bookReport')


# 图书报表处理功能：
# 1.查询每月被借出的图书数量
@api.route('/month_borrow_count', methods=['GET'])
def get_month_borrow_count():
    month_borrow_count = Record.query.with_entities(
        func.date_format(Record.startdate, '%Y-%m').label('month'),
        func.count(Record.bookid).label('borrow_count')
    ).group_by(func.date_format(Record.startdate, '%Y-%m')).all()
    print(month_borrow_count)
    if month_borrow_count is None:
        return Result.error(404, 'book records not found')
    else:
        return Result.success(json.dumps(dict(month_borrow_count)))


# 2.每月被借出的图书列表
@api.route('/month_borrow_book', methods=['GET'])
def get_month_borrow_book():
    month_borrow_book = Record.query.with_entities(
        func.date_format(Record.startdate, '%Y-%m').label('month'),
        Book.bookname,
        func.count(Record.bookid).label('borrow_count')
    ).join(Book, Record.bookid == Book.bookid).group_by('month', Book.bookname
                                                        ).having(func.count(Record.bookid) > 0
                                                                 ).all()

    print(month_borrow_book)
    # 将查询结果转换为字典列表
    result_list = []
    for row in month_borrow_book:
        result_list.append({
            'month': row.month,
            'bookname': row.bookname,
            'borrow_count': row.borrow_count
        })
    # 将字典列表转换为 JSON 格式
    json_data = json.dumps(result_list)
    if month_borrow_book is None:
        return Result.error(404, 'book records not found')
    else:
        return Result.success(json_data)


# 3.每月借书的用户,按借书数量的大小排序
@api.route('/month_borrow_user', methods=['GET'])
def get_month_borrow_user():
    month_borrow_user = Record.query.with_entities(
        func.date_format(Record.startdate, '%Y-%m').label('month'),
        Record.userid,
        func.count(Record.bookid).label('borrow_count')
    ).group_by('month', Record.userid
               ).order_by(func.count(Record.bookid).desc()
                          ).all()

    print(month_borrow_user)
    # 将查询结果转换为字典列表
    result_list = []
    for row in month_borrow_user:
        result_list.append({
            'month': row.month,
            'userid': row.userid,
            'borrow_count': row.borrow_count
        })
    # 将字典列表转换为 JSON 格式
    json_data = json.dumps(result_list)

    if month_borrow_user is None:
        return Result.error(404, 'book records not found')
    else:
        return Result.success(json_data)
